package com.fd.dao.impl;

import com.fd.dao.ITestJDBC;
import com.fd.entity.Student;
import com.fd.myException.MyException;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

@Service
public class TestJDBC extends BaseJDBC implements ITestJDBC {
    static Connection con;
    PreparedStatement ps;
    Statement statement;
    ResultSet resultSet;
    Scanner scanner;


    static {
        MyException myException = new MyException();
        try {
            con = connection();
            System.out.println("con===================" + con);
        } catch (IOException | ClassNotFoundException e) {
            myException.catchEx(e);
        } catch (SQLException throwables) {
            myException.catchEx(throwables);
        }
    }

    //关闭
    public void close() {
        try {
            if (null != scanner) {
                scanner.close();
            }
            if (null != resultSet) {
                resultSet.close();
            }
            if (null != statement) {
                statement.close();
            }
            if (null != ps) {
                ps.close();
            }
            if (null != con) {
                con.close();
            }
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }

    }


    //获取最大学生id
    public Integer getMaxId() {
        try {
            ps = con.prepareStatement("select sid from student");
            resultSet = ps.executeQuery();
            Integer max = 0;
            while (resultSet.next()) {
                String sid = resultSet.getString(1);
                System.out.println("sid================"+sid);
                Integer id = Integer.valueOf(sid.split("_")[1]);
                if (id > max) {
                    max = id;
                }
            }
            return max;
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }
        return null;
    }

    //    添加
    public void add(Student newStudent) {
        try {
            ps = con.prepareStatement("insert into student values (?,?,?,?,?,?)");
            int index = 0;
            ps.setObject(++index, newStudent.getsId());
            ps.setObject(++index, newStudent.getName());
            ps.setObject(++index, newStudent.getAge());
            ps.setObject(++index, newStudent.getGender());
            ps.setObject(++index, newStudent.getAddress());
            ps.setObject(++index, newStudent.getClasses());
            ps.execute();
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }
    }

    //获取所有id
    public List getAllId() {
        ArrayList<String> sid = new ArrayList<>();
        try {
            ps = con.prepareStatement("select sid from student");
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                sid.add(resultSet.getString(1));
            }
            return sid;
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }
        return new ArrayList<String>();
    }

    //    修改
    public void alterStudent(Student alterStudent) {
        try {
            ps = con.prepareStatement("update student set sname=?,age=?,gender=?,address=?,classes=? where sid=?");
            int index = 0;
            ps.setObject(++index, alterStudent.getName());
            ps.setObject(++index, alterStudent.getAge());
            ps.setObject(++index, alterStudent.getGender());
            ps.setObject(++index, alterStudent.getAddress());
            ps.setObject(++index, alterStudent.getClasses());
            ps.setObject(++index, alterStudent.getsId());
            ps.execute();
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }
    }

    //根据id删除学生
    public void delStudent(String delId) {
        try {
            ps = con.prepareStatement("delete from student where sid=?");
            ps.setObject(1, delId);
            ps.execute();
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
        }
    }

    @Override
//    条件查询
    public List query(String searchVlaue) {
        try {
            PreparedStatement createps = con.prepareStatement("CREATE TABLE if not exists  student (sid CHAR(6) NOT NULL,sname VARCHAR(10) DEFAULT NULL,age INT(11) DEFAULT NULL,gender VARCHAR(10) DEFAULT NULL,address VARCHAR(10) DEFAULT NULL,classes VARCHAR(10) DEFAULT '1904',PRIMARY KEY (`sid`))");
            createps.execute();
            System.out.println("搜索参数为：          " + searchVlaue);
            ps = con.prepareStatement("select * from student where SNAME like ?");
            ps.setObject(1, "%" + searchVlaue + "%");
            resultSet = ps.executeQuery();
            List<Student> searchStudents = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3), resultSet.getString(4), resultSet.getString(5), resultSet.getInt(6));
                searchStudents.add(student);
            }
            return searchStudents;
        } catch (SQLException throwables) {
            new MyException().catchEx(throwables);
            return new ArrayList<>();
        }
    }
}